package org.dashboard.service;

import com.alibaba.dubbo.config.annotation.Reference;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.dashboard.bean.AdDataset;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Copyright © 2018. All rights reserved.
 *
 * @Author: FreezeSoul
 * @CreateDate: 2018/8/28 9:39
 * @Description: ExcelUtilService
 * @Version: V1.0
 */
@Component
public class ExcelUtilService {

    private static final Logger logger = LoggerFactory.getLogger(ExcelUtilService.class);

    private static final String EXCEL_FOLDER = "excel";
    private static final String EXCEL_FOLDER_DELETE = "DELETE";

    @Value("${web.upload.base.path}")
    private String uploadBasePath;


    @Reference
    private AdExcelService adExcelService;

    @SuppressWarnings("Duplicates")
    public List<Map<String, Object>> loadChartExcelData(AdDataset adDataset) {
        List<Map<String, Object>> data = new ArrayList<>();
        InputStream is = null;
        try {
            JSONObject datasetObj = (JSONObject) JSON.parse(adDataset.getDatasetData());
            if (datasetObj == null) return data;
            String fullPath = uploadBasePath + File.separator + EXCEL_FOLDER;
            String excelFileName = datasetObj.getJSONObject("DataServiceMeta").getString("ExcelFileName");
            String excelFullPath = fullPath + File.separator + excelFileName;
            is = new FileInputStream(excelFullPath);
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
            if (xssfWorkbook.getNumberOfSheets() > 0) {
                XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
                if (xssfSheet != null && xssfSheet.getLastRowNum() >= 1) {
                    XSSFRow firstRow = xssfSheet.getRow(0);
                    for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
                        XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                        Map<String, Object> rowMap = new HashMap<>();
                        for (int cellNum = 0; cellNum < xssfRow.getLastCellNum(); cellNum++) {
                            XSSFCell cell = xssfRow.getCell(cellNum);
                            switch (cell.getCellTypeEnum()) {
                                case STRING:
                                    rowMap.put(firstRow.getCell(cellNum).toString(), cell.getStringCellValue());
                                    break;
                                case NUMERIC:
                                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                        DataFormatter df = new DataFormatter();
                                        rowMap.put(firstRow.getCell(cellNum).toString(), df.formatCellValue(cell));
                                    } else {
                                        rowMap.put(firstRow.getCell(cellNum).toString(), cell.getNumericCellValue());
                                    }
                                    break;
                                default:
                                    rowMap.put(firstRow.getCell(cellNum).toString(), cell.getStringCellValue());
                                    break;
                            }
                        }
                        data.add(rowMap);
                    }
                }
            }
        } catch (IOException e) {
            logger.error("loadChartExcelData", e);
        } finally {
            try {
                if (is != null)
                    is.close();
            } catch (IOException e) {
                logger.error("loadChartExcelData", e);
            }
        }
        return data;
    }



    @SuppressWarnings("Duplicates")
    public Map<String, Object> parseExcelData(String excelFullPath, String fileJustName, String diskFileName) {
        Map<String, Object> allData = new HashMap<>();
        List<Object[]> excelData = new ArrayList<>();
        InputStream is = null;
        try {
            is = new FileInputStream(excelFullPath);
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
            if (xssfWorkbook.getNumberOfSheets() > 0) {
                XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
                if (xssfSheet != null) {
                    String excelSheetName = xssfSheet.getSheetName();
                    if (xssfSheet.getLastRowNum() >= 1) {
                        // First Row
                        XSSFRow xssfFirstRow = xssfSheet.getRow(0);
                        List<Object> rowFirstList = new ArrayList<>();
                        for (Cell cell : xssfFirstRow) {
                            rowFirstList.add(cell.getStringCellValue());
                        }
                        excelData.add(rowFirstList.toArray());
                        // Data Row
                        for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
                            XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                            List<Object> rowList = new ArrayList<>();
                            for (Cell cell : xssfRow) {
                                switch (cell.getCellTypeEnum()) {
                                    case STRING:
                                        rowList.add(cell.getStringCellValue());
                                        break;
                                    case NUMERIC:
                                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                            DataFormatter df = new DataFormatter();
                                            rowList.add(df.formatCellValue(cell));
                                        } else {
                                            rowList.add(cell.getNumericCellValue());
                                        }
                                        break;
                                    case FORMULA:
                                        rowList.add(cell.getNumericCellValue());
                                        break;
                                    default:
                                        rowList.add(cell.getStringCellValue());
                                        break;
                                }
                            }
                            excelData.add(rowList.toArray());
                        }

                        if (excelData.size() > 1) {
                            Integer[] ids = adExcelService.importExcelSchema(fileJustName, excelSheetName, diskFileName, excelData.subList(0, 2));

                            if (ids != null && ids.length == 2) {
                                allData.put("schemaId", ids[0]);
                                allData.put("dataSetId", ids[1]);
                                allData.put("data", excelData);
                            }
                        }
                    }
                }
            }
        } catch (IOException e) {
            logger.error("parseExcelData", e);
        } finally {
            try {
                if (is != null)
                    is.close();
            } catch (IOException e) {
                logger.error("parseExcelData", e);
            }
        }
        return allData;
    }



    @SuppressWarnings("Duplicates")
    public List<Object[]> loadExcelData(String excelFullPath) {
        List<Object[]> excelData = new ArrayList<>();
        InputStream is = null;
        try {
            is = new FileInputStream(excelFullPath);
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
            if (xssfWorkbook.getNumberOfSheets() > 0) {
                XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
                if (xssfSheet != null) {
                    Boolean isFirstRow = true;
                    for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
                        XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                        List<Object> rowList = new ArrayList<>();
                        for (Cell cell : xssfRow) {
                            if (isFirstRow) {
                                rowList.add(cell.getStringCellValue());
                            } else {
                                switch (cell.getCellTypeEnum()) {
                                    case STRING:
                                        rowList.add(cell.getStringCellValue());
                                        break;
                                    case NUMERIC:
                                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                            DataFormatter df = new DataFormatter();
                                            rowList.add(df.formatCellValue(cell));
                                        } else {
                                            rowList.add(cell.getNumericCellValue());
                                        }
                                        break;
                                    case FORMULA:
                                        rowList.add(cell.getNumericCellValue());
                                        break;
                                    default:
                                        rowList.add(cell.getStringCellValue());
                                        break;
                                }
                            }
                        }
                        isFirstRow = false;
                        excelData.add(rowList.toArray());
                    }
                }
            }
        } catch (IOException e) {
            logger.error("loadExcelData", e);
        } finally {
            try {
                if (is != null)
                    is.close();
            } catch (IOException e) {
                logger.error("loadExcelData", e);
            }
        }
        return excelData;
    }




    @SuppressWarnings("Duplicates")
    public void updateExcelData(String excelOldFullPath, String excelNewFullPath, List<Object[]> excelData) {
        OutputStream os = null;
        try {
            File deleteExcelFile = new File(excelOldFullPath);
            if (deleteExcelFile.isFile()) {
                String moveToPath = deleteExcelFile.getParent() + File.separator + EXCEL_FOLDER_DELETE;
                File moveTo = new File(moveToPath);
                if (!moveTo.exists()) {
                    if (!moveTo.mkdir())
                        return;
                }

                String moveToFilePath = moveToPath + File.separator + deleteExcelFile.getName();
                File moveToFile = new File(moveToFilePath);
                if (!deleteExcelFile.renameTo(moveToFile))
                    return;
            }

            os = new FileOutputStream(excelNewFullPath);
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
            Sheet createSheet = xssfWorkbook.createSheet();
            Integer rowIndex = 0;
            for (Object[] objArray : excelData) {
                Row row = createSheet.createRow(rowIndex++);
                Integer cellIndex = 0;
                for (Object obj : objArray) {
                    Cell cell = row.createCell(cellIndex++);
                    cell.setCellValue(obj.toString());
                }
            }
            xssfWorkbook.write(os);
        } catch (IOException e) {
            logger.error("updateExcelData", e);
        } finally {
            try {
                if (os != null)
                    os.close();
            } catch (IOException e) {
                logger.error("updateExcelData", e);
            }
        }
    }
}
